package icasue.tools.sqlhelper.core;


import icasue.tools.sqlhelper.core.AbstractSqlProviderFrame;
import icasue.tools.sqlhelper.core.DBCol;
import icasue.tools.sqlhelper.core.KeyUsingRate;
import icasue.tools.sqlhelper.core.Frame;
import icasue.tools.sqlhelper.core.SqlBuild;
import lombok.SneakyThrows;
import icasue.reflect.annotations.NotNull;
import icasue.reflect.annotations.Nullable;
import icasue.reflect.handles.fields.FieldOF;
import icasue.reflect.handles.method.MethodOF;
import icasue.tools.sqlhelper.annotation.Index;
import icasue.tools.sqlhelper.annotation.IndexG;
import icasue.tools.sqlhelper.annotation.Primary;
import icasue.tools.sqlhelper.annotation.Unique;
import icasue.tools.sqlhelper.common.FieldsTouch;

import java.lang.annotation.Annotation;
import java.lang.invoke.SerializedLambda;
import java.lang.reflect.Field;
import java.util.*;
import java.util.function.Consumer;
import java.util.function.Function;
import java.util.stream.Collectors;
import java.util.stream.Stream;

import static icasue.tools.sqlhelper.common.Const.*;

abstract class FrameParser extends SqlBuild {

    // return there elements:
    // { key_col_name, value, using_rate }
    @Nullable
    @SneakyThrows(Throwable.class)
    final static Object[] _keyValueRate(@NotNull Object inst,
                                 @NotNull DBCol dbCol,
                                 @NotNull AbstractSqlProviderFrame frame,
                                 @NotNull final Class<? extends Annotation> type) {
        Object[] result = null;
        if(type == Primary.class){
            if(dbCol.primary != null){
                // primary index only declare column self, return : Object[0]{"col",value}
                result = new Object[3];
                result[0] = dbCol.column;
                result[1] = dbCol.field.get(inst);
                // real index using rate.
                result[2] = (result[1] != null ? 1 : 0) * /*dbCol.primary.weight()*/ 3.0f;
            }
        }else if(type == Unique.class){
            if(dbCol.unique != null){
                // unique index only declare column self, return : Object[0]{"col",value}
                result = new Object[3];
                result[0] = dbCol.column;
                result[1] = dbCol.field.get(inst);
                // real index using rate.
                result[2] = (result[1] != null ? 1 : 0) * /*dbCol.unique.weight()*/ 2.0f;
            }
        }else if(type == Index.class){
            if(dbCol.index != null){
                // normal index only declare column self, return : Object[0]{"col",value}
                result = new Object[3];
                result[0] = dbCol.column;
                result[1] = dbCol.field.get(inst);
                // real index using rate.
                Index index = dbCol.index;
                result[2] = (result[1] != null ? 1 : 0) *
                        (index.weight() < 0.0f ? 0.0f : (index.weight() > 1.0f ? 1.0f : index.weight()));
            }
        }else {
            result = null;
        }
        return result;
    };



    // return three element:
    // { {index1,index2,index3}, {val1,val2,val3}, using_rate }
    @Nullable
    @SneakyThrows(Throwable.class)
    final static Object[] _keyValuesRate(@NotNull Object inst,
                               @NotNull AbstractSqlProviderFrame frame,
                               @NotNull IndexG indexG) {
        String[] keys_sorted = indexG.value();
        Map<String,DBCol> mappingRoot = frame.dbTab.mappingRoot;
        Object[] result = null;
        if(keys_sorted.length > 0){
            // init result space three.
            result = new Object[3];
            // create values space.
            Object[] values_sorted = Stream.of(keys_sorted)
                    .map(one_key -> FieldOF.get_.apply(mappingRoot.get(one_key).getField(), inst))
                    .toArray();
            /* calculate mixed index using rate. */
            float _weight = (indexG.weight() < 0.0f ? 0.0f : (indexG.weight() > 1.0f ? 1.0f : indexG.weight()));
            // calculate _logic_index_using_rate_step.
            float _logic_index_using_rate_step = 1.0f / keys_sorted.length;
            // calculate _logic_index_using_rate_step.
            float _logic_index_using_rate = 0;
            // sum _logic_index_using_rate_step.
            for (Object one_group_value : values_sorted) {
                if(one_group_value != null){
                    _logic_index_using_rate += _logic_index_using_rate_step;
                }else {
                    break;
                }
            }
            // calculate _real_index_using_rate: _logic_index_using_rate * indexG's weight.
            final float _real_index_using_rate = _logic_index_using_rate * _weight;

            result[0] = keys_sorted;
            result[1] = values_sorted;
            result[2] = _real_index_using_rate;
        }else {
            result = null;
        }
        return result;
    };


    /**
     * 核心思想:
     *      在面向对象进行数据库查询时，动态SQL生成会充分考虑索引的情况，以试图发送最优的数据库DQL、DML交由执行引擎，以保证数据库查询的性能.
     * 1.索引选择的原则：
     *      (主键索引 = 主键索引)  >  (唯一索引 = 唯一索引)  >  (普通索引 索引使用率高 > 索引使用率低)  >  (不索引列 = 不索引列)
     * 2.索引使用率：
     *
     *      *** (最左原则索引使用率跨度 * 满足索引最左原则生效列数 * 索引权重)
     *      索引类型     权重      列唯一性(n)   最左原则该索引有效索引列数(最左连续非空)    索引使用率跨度运算公式     真实索引使用率
     *      主键索引     3.0f          1                1                             1.0f / 1                (1.0f / 1) * 1 * 3.0f
     *      唯一索引     2.0f          1                1                             1.0f / 1                (1.0f / 1) * 1 * 2.0f
     *      普通索引     (0.0f-1.0f]   1                1                             1.0f / 1                (1.0f / 1) * 1 * (0.0f-1.0f]
     *      聚簇索引     (0.0f-1.0f]   n                [1-n]                         1.0f / n                (1.0f / n) * [1-n] * (0.0f-1.0f]
     *      非索引       0.0f          1                1                             1.0f / 1                (1.0f / 1) * 1 * 0.0f
     * 3.程序设计：
     *      1.过滤
     *              获取数据库查询对象DO的有效数据库映射字段
     *             （包含索引列 非索引列）
     *      2.Primary索引选择
     *              读取与解析参与查询的有效主键字段.
     *      3.Unique索引选择
     *              读取与解析参与查询的有效Unique字段.
     *      4.Index索引策略选择
     *              循环对普通索引与聚簇索引组进行解析与权值运算，每次选择最优的一组索引，并对后续抉择进行排异.
     *             （主键索引，唯一索引权值固定，索引策略权重的运算主要发生在普通索引/聚簇索引上）
     *      5.非索引/排异聚簇索引选择
     *              对非主键，非Unique，非Index，被4阶段索引决策排异的有效查询列，统一按照非索引进行处理(权值0.0f)
     *      6.描述最终查询策略
     *              合并2，3，4，5的执行结果，为最终的查询策略[描述阶段].
     *      7.预生成查询条件语句     [USER_DEFINED]
     *              将6解析成有序查询条件(段)
     *      8.生成动态SQL.          [USER_DEFINED]
     *              使用7阶段的查询条件(段)与frame中声明的库表路由，生成最终发送给JDBC驱动的SQL.
     *
     */

    /**
     * 智能条件决策.
     * @param inst 用于描述数据库条件的Java实例.
     * @param frame 骨架.
     * @return
     */
    final static List<KeyUsingRate> _wisdomConditions(@NotNull Object inst,
                                                      @NotNull AbstractSqlProviderFrame frame){
        /* PART 1 */
        // 数据库交互实例选择性copy.
        final Object _copy_inst = Frame._copySelective(frame, inst);

        // 最终的排序条件.
        final List<KeyUsingRate> finalSortedConditions = new LinkedList<>();

        // KeyUsingRate 降序排序比较器.
        final Comparator<KeyUsingRate> comparator_desc = (left, right) -> Float.compare(left.usingRate, right.usingRate) * -1;

        // 全量数据库字段映射.
        final Map<String,DBCol> mappingRoot = frame.dbTab.mappingRoot;

        // 存在有效value的数据库字段与java类型的映射.
        final Map<String,DBCol> inValidMapping = new HashMap<>(mappingRoot.size());

        // mark wait clear mapping.
        final List<String> waitClears = new LinkedList<>();

        // inValidMapping clear function.
        final Consumer<List<String>> $clearMapping = (columns) -> {
            columns.stream().forEach(column -> inValidMapping.remove(column));
            columns.clear();
        };

        // 存在有效value的数据库字段与值的映射.
        final Map<String, Object> inValidValueMapping = mappingRoot.entrySet().stream()
                .map(fieldMap -> {
                    String dbField = fieldMap.getKey();
                    DBCol dbCol = fieldMap.getValue();
                    Field javaField = dbCol.field;
                    Object val = FieldOF.get_.apply(javaField, _copy_inst);
                    return new Object[]{dbField, val};
                })
                // filter inValidMapping to value.
                .filter(col_val -> col_val[1] != null)
                // filling inValidMapping for DBCol.
                .peek(col_val -> inValidMapping.put(String.valueOf(col_val[0]), mappingRoot.get(col_val[0])))
                .collect(Collectors.toMap(col_val -> String.valueOf(col_val[0]), col_val -> col_val[1]));

        // 获取table聚簇索引声明.
        final @NotNull IndexG[] indexGrp = Optional.ofNullable(frame.dbTab.indexGrp).orElseGet(() -> new IndexG[0]);

        /* *************  构建查询条件序列: (主键 > 唯一索引 > 普通索引 / 聚簇索引 > 非索引) ***************/

        /* PART 2 */
        if(Boolean.TRUE){
            try {
                // 主键索引.
                List<KeyUsingRate> primaryKeysUsingRates = inValidMapping.values().stream()
                        .filter(dbCol -> dbCol.primary != null)
                        .map(dbCol -> {
                            @NotNull
                            Object[] key_value_usingRate = _keyValueRate(_copy_inst, dbCol, frame, Primary.class);
                            KeyUsingRate keyUsingRate = KeyUsingRate.builder()
                                    .keys(new String[]{(String) key_value_usingRate[0]})
                                    .values(new Object[]{key_value_usingRate[1]})
                                    .usingRate((float) key_value_usingRate[2])
                                    .build();

                            // mark clear mapping.
                            waitClears.add(dbCol.column);
                            // clear copy instance primary key field's value.
                            FieldOF.set_.accept(dbCol.field, _copy_inst, null);
                            return keyUsingRate;
                        })
                        .collect(Collectors.toList());

                primaryKeysUsingRates.sort(comparator_desc);
                finalSortedConditions.addAll(primaryKeysUsingRates);
            }finally {
                $clearMapping.accept(waitClears);
            }
        }

        /* PART 3 */
        if (Boolean.TRUE){
            try {
                // 唯一索引
                List<KeyUsingRate> uniqueKeysUsingRates = inValidMapping.values().stream()
                        .filter(dbCol -> dbCol.unique != null)
                        .map(dbCol -> {
                            @NotNull
                            Object[] key_value_usingRate = _keyValueRate(_copy_inst, dbCol, frame, Unique.class);
                            KeyUsingRate keyUsingRate = KeyUsingRate.builder()
                                    .keys(new String[]{(String) key_value_usingRate[0]})
                                    .values(new Object[]{key_value_usingRate[1]})
                                    .usingRate((float) key_value_usingRate[2])
                                    .build();

                            // mark clear mapping.
                            waitClears.add(dbCol.column);
                            // clear copy instance unique key field's value.
                            FieldOF.set_.accept(dbCol.field, _copy_inst, null);
                            return keyUsingRate;
                        })
                        .collect(Collectors.toList());

                uniqueKeysUsingRates.sort(comparator_desc);
                finalSortedConditions.addAll(uniqueKeysUsingRates);
            }finally {
                $clearMapping.accept(waitClears);
            }
        }

        /* PART 4 */
        if(Boolean.TRUE){
            // 对单列索引和聚簇索引合并排序的容器.
            final List<KeyUsingRate> combineKeyUsingRates = new ArrayList<>();

            /*
             * 循环合并聚簇索引与单列索引，每次选择索引真实使用率最高的一组索引为主，
             * 并提供必要的选择偏向 -> (当前所选择的索引需要的有效查询列不参与后续索引选择)
             * 从而形成最终的查询索引条件序列.
             *       例如：
             *           有索引:
             *               name 使用率 0.1
             *               age 使用率 0.01
             *               sex 使用率 0.01
             *           有索引组:
             *               (name,sex) 使用率 0.6,
             *               (name,age)  使用率 0.5
             *               (name,birth)  使用率 0.4
             *               (birth,hobby)  使用率 0.3
             *       查询列: name = 'Jon', age = 10, sex = 'man', birth = '1996-09-18', hobby = '[thinking,coding]'
             *       本次最优索引选择: (name,sex);
             *       下次最优索引选择: (birth,hobby); 而不去选择使用率更高的 (name,age) 和 (name,birth) 因为name,sex字段会影响此前决策出的最优索引，
             *                       因此name,sex不参与下次的索引选择 (实现手段: 排异 [set name = null, set sex = null])，
             *                       同时当索引组的[非末位]出现了null值，也会减小后续索引序列决策流程的复杂度.
             * */

            KeyUsingRate luckyElement = null;
            do {
                // 聚簇索引.
                List<KeyUsingRate> mixedIndexGroupUsingRate = Stream.of(indexGrp)
                        .filter(group -> group.value().length > 0)
                        .map(group -> {
                            Object[] cols_values_usingRate = _keyValuesRate(_copy_inst, frame, group);
                            return KeyUsingRate.builder()
                                    .keys((String[]) cols_values_usingRate[0])
                                    .values((Object[]) cols_values_usingRate[1])
                                    .usingRate((float) cols_values_usingRate[2])
                                    .build();
                        })
                        .collect(Collectors.toList());

                // 普通索引.
                List<KeyUsingRate> normalIndexGroupUsingRate = inValidMapping.entrySet().stream()
                        .filter(col_mapping -> col_mapping.getValue().index != null)
                        .map(col_mapping ->  _keyValueRate(_copy_inst, col_mapping.getValue(), frame, Index.class))
                        .map(key_value_usingRate ->
                                KeyUsingRate.builder()
                                        .keys(new String[]{(String) key_value_usingRate[0]})
                                        .values(new Object[]{key_value_usingRate[1]})
                                        .usingRate((float) key_value_usingRate[2])
                                        .build()
                        )
                        .collect(Collectors.toList());

                // 合并排序.
                combineKeyUsingRates.clear();
                combineKeyUsingRates.addAll(mixedIndexGroupUsingRate);
                combineKeyUsingRates.addAll(normalIndexGroupUsingRate);
                combineKeyUsingRates.sort(comparator_desc);

                // 最优索引选择.
                if(combineKeyUsingRates.size() != 0 && (luckyElement = combineKeyUsingRates.get(0)) != null && luckyElement.usingRate > 0.0f){
                    // 本次排序存在有效的索引选择(索引使用率 > 0.0f)
                    finalSortedConditions.add(luckyElement);
                    /*清理该索引使用到的属性值(防止后续选择影响该索引), 对于聚簇索引只清理有效索引位(满足最左原则)的值，
                      无效索引位后的有效映射仍可参与后续的索引选择，例如有索引: (name,age,sex) = ('casue',null,'man')
                      此时有效映射 'sex' 仍可参与后续的索引选择!*/
                    for (int i = 0; i < luckyElement.values.length; i++) {
                        if(luckyElement.values[i] == null){
                            break;
                        }else {
                            FieldOF.set_.accept(mappingRoot.get(luckyElement.keys[i]).field, _copy_inst, null);
                        }
                        // clear mapping.
                        inValidMapping.remove(luckyElement.keys[i]);
                    }
                }else {
                    luckyElement = null;
                }

            }while (luckyElement != null);
        }

        /* PART 5 */
        if(Boolean.TRUE){
            try {
                // 非索引 或 声明了聚簇索引 但[权值不够] 且[被排异] 后的条件, (按照非索引处理，权值0.0f)
                inValidMapping.values().stream().forEach(dbCol -> {
                    KeyUsingRate unIndexed = KeyUsingRate.builder()
                            .keys(new String[]{dbCol.column})
                            .values(new Object[]{inValidValueMapping.get(dbCol.column)})
                            .usingRate(0.0f)
                            .build();
                    finalSortedConditions.add(unIndexed);
                    // mark clear mapping.
                    waitClears.add(dbCol.column);
                });
            }finally {
                $clearMapping.accept(waitClears);
            }
        }

        /* PART 6 */
        return finalSortedConditions;
    }

    /**
     * 获取Frame中java属性到数据库列的映射.
     */
    final static Function<AbstractSqlProviderFrame, Map<String,String>> $fName_To_Column = frame -> frame.dbTab.mappingRoot.entrySet().stream()
            .collect(Collectors.toMap(kv -> kv.getValue().field.getName(), kv -> kv.getKey()));

    /**
     * 查询java属性映射到数据库字段进行查询 [`column`]
     * strict 用于控制是否查询的列为存在显示声明的数据库列 [max()] [now()]等将会被过滤.
     * asJava 是否保留 'as javaField' 子句，[`column` as fName]
     */
    final static List<String> _fNamesToQueryColumns(@NotNull String[] fNames, @NotNull AbstractSqlProviderFrame frame, boolean strict, boolean asJava){
        final Map<String, String> field_column = $fName_To_Column.apply(frame);
        return Stream.of(fNames).map(fName -> {
            if(field_column.containsKey(fName)){
                if(asJava){
                    // `column` as field`
                    return new StringBuilder()
                            .append($STRICT).append(field_column.get(fName)).append($STRICT).append($SPACE)
                            .append(AS).append($SPACE)
                            .append(fName).toString();
                }else {
                    return new StringBuilder()
                            .append($STRICT).append(field_column.get(fName)).append($STRICT)
                            .toString();
                }
            }else {
                if(!strict){
                    // field or database's native function such as now()
                    // or the new column generic by temp function query such as 'substring(column,1,5) as subCol'.
                    return fName;
                }else {
                    return null;
                }
            }
        }).filter(col_map -> col_map != null)
                .collect(Collectors.toList());
    }


    /**
     * 查询java属性映射到数据库字段进行查询 [`column`]
     * strict 用于控制是否查询的列为存在显示声明的数据库列 [max()] [now()]等将会被过滤.
     * asJava 是否保留 'as javaField' 子句，[`column` as fName]
     */
    final static List<String> _fNamesToQueryColumns(@NotNull Collection<String> fNames, @NotNull AbstractSqlProviderFrame frame, boolean strict, boolean asJava){
        final Map<String, String> field_column = $fName_To_Column.apply(frame);
        return fNames.stream().map(fName -> {
            if(field_column.containsKey(fName)){
                if(asJava){
                    // `column` as field`
                    return new StringBuilder()
                            .append($STRICT).append(field_column.get(fName)).append($STRICT).append($SPACE)
                            .append(AS).append($SPACE)
                            .append(fName).toString();
                }else {
                    return new StringBuilder()
                            .append($STRICT).append(field_column.get(fName)).append($STRICT)
                            .toString();
                }
            }else {
                if(!strict){
                    // field or database's native function such as now()
                    // or the new column generic by temp function query such as 'substring(column,1,5) as subCol'.
                    return fName;
                }else {
                    return null;
                }
            }
        }).filter(col_map -> col_map != null)
                .collect(Collectors.toList());
    }

    /**
     * 转换由lambda描述的属性到数据库字段.
     * @param frame 骨架
     * @param strict 是否要求标准化列 `col`
     * @param fieldsTouch
     * @return
     */
    final static List<String> _lambdaFieldsToColumns(AbstractSqlProviderFrame frame, boolean strict, @NotNull FieldsTouch... fieldsTouch){
        final Map<String, String> fNameToColumn = $fName_To_Column.apply(frame);
        List<String> columns = new ArrayList<>(fieldsTouch.length);
        for (FieldsTouch fieldTouch : fieldsTouch) {
            if(fieldTouch != null){
                // parse lambda to field,
                SerializedLambda serializedLambda = (SerializedLambda)MethodOF.invoke_mType_mName_inst_paramArray.apply(fieldTouch.getClass(), "writeReplace", fieldTouch, null);
                String implMethodName_getField = serializedLambda.getImplMethodName();
                if(implMethodName_getField.length() > 3){
                    String FieldName = implMethodName_getField.substring(3);
                    char[] toChars = FieldName.toCharArray();
                    toChars[0] = Character.toLowerCase(toChars[0]);
                    String fieldName = new String(toChars);
                    if(fNameToColumn.containsKey(fieldName)){
                        columns.add(fNameToColumn.get(fieldName));
                    }else if(fNameToColumn.containsKey(FieldName)){
                        columns.add(fNameToColumn.get(FieldName));
                    }else {
                        /*Ignore this not mapping field.*/
                    }
                }
            }
        }
        if(strict){
            columns = columns.stream().map(column -> new StringBuilder().append($STRICT).append(column).append($STRICT).toString())
                    .collect(Collectors.toList());
        }
        return columns;
    }

}